do "E:\yungyu\lottery_baby\do\set_environment.do"

clear
set more off

cap program drop summary_stat
program define summary_stat

syntax [if] [in] [aw fw iw pw], col(string) save(string)

	preserve

	marksample touse
	qui keep if `touse'

	collapse 	(mean)age (sd)age_sd = age ///
				(mean)resident_urban (sd)resident_urban_sd = resident_urban ///
				(mean)female (sd)female_sd = female ///
				(mean)married (sd)married_sd = married ///
				(mean)pers_labor (sd)pers_labor_sd = pers_labor ///
				(mean)pers_earnings (sd)pers_earnings_sd = pers_earnings ///
				(mean)pers_income (sd)pers_income_sd = pers_income ///	
				(mean)pers_asset (sd)pers_asset_sd = pers_asset ///
				(mean)pers_liquidasset (sd)pers_liquidasset_sd = pers_liquidasset ///
				(mean)pers_saving (sd)pers_saving_sd = pers_saving ///
				(mean)tot_earnings (sd)tot_earnings_sd = tot_earnings ///
				(mean)tot_income (sd)tot_income_sd = tot_income ///	
				(mean)tot_asset (sd)tot_asset_sd = tot_asset ///
				(mean)tot_liquidasset (sd)tot_liquidasset_sd = tot_liquidasset ///
				(mean)tot_saving (sd)tot_saving_sd = tot_saving ///						
				(mean)totbaby (sd)totbaby_sd = totbaby ///
				(mean)pre1born (sd)pre1born_sd = pre1born ///
				(mean)pre2born (sd)pre2born_sd = pre2born ///
				(mean)pre3born (sd)pre3born_sd = pre3born ///
				(count)N = n [aw=w]
	
	xpose, clear v
	order _var
	rename v1 `col'
	rename _var variables
	tostring `col', replace force format(%15.3fc)
	replace `col' = "(" + `col' + ")" if strpos(var,"_sd")~=0
	replace variables = "" if strpos(var,"_sd")~=0
	
	save "`save'", replace
	
	restore
end


use "$wdata/population.dta", clear

replace age = age - 1

foreach var in income earnings asset liquidasset saving{
	gen tot_`var' = pers_`var' + spouse_`var'
}

gen resident_urban = resident_t == 1 | resident_m == 1
gen female = gender == 2

gen prize10M = prize
gen anyprize = prize > 0
rename totbaby_pre1 totbaby

gen n = 1
gen w = 1

** Household variables
foreach var in saving{
	gen avg_`var' = pers_`var' if spouse_idn == ""
	replace avg_`var' = (pers_`var' + spouse_`var')/2 if spouse_idn ~= ""
}
		
gen pers_labor = pers_earnings > 0
gen avg_labor  = avg_earnings>0	
	
summary_stat, col(Population) save("$table/dta/Tab1_Population.dta")

*********************************************************************

foreach x in 2 5 50{
use "$wdata\project_lottery_fertility_b3a6_first_`x'k.dta", clear

** Household variables	
foreach var in income earnings asset liquidasset saving{
	gen tot_`var' = pers_`var' + spouse_`var'
}


foreach var in saving{
	gen avg_`var' = pers_`var' if spouse_idn == ""
	replace avg_`var' = (pers_`var' + spouse_`var')/2 if spouse_idn ~= ""
}

forv i = 0(1)3{
	gen v1 = baby > 0 if norm == -`i'
	egen pre`i'born = mean(v1), by(houseid)
	drop v1
	gen v1 = pers_real_prize_aftertax if norm == -`i'
	egen pre`i'prize = mean(v1), by(houseid)
	drop v1
	recode pre`i'prize . = 0
}

gen resident_urban = resident_t == 1 | resident_m == 1
gen female = pers_gender == 2

gen pers_labor = pers_earnings > 0
gen avg_labor  = avg_earnings > 0	

gen prize = winyr_pers_real_prize_aftertax
replace prize = pre0prize if current == 0

summary_stat if norm_year == -1 & prize10M <= 5 , col(`x'k) save("$table/dta/Tab1_`x'k.dta")
}

use "$table/dta/Tab1_5k.dta", clear
merge 1:1 _n using "$table/dta/Tab1_2k.dta", nogen
merge 1:1 _n using "$table/dta/Tab1_50k.dta", nogen
merge 1:1 _n using "$table/dta/Tab1_Population.dta", nogen
 
export excel "$table/Tables.xlsx", sheet("Tab1") sheetreplace first(var)
